package com.tsd.core.excel.impexcel;

import com.tsd.core.utils.HlpUtils;
import org.apache.poi.ss.usermodel.*;

import java.text.NumberFormat;
import java.util.Date;


/**
 * @author Hillpool
 */
public class PoiExcelUtil {

    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    /**
     * 创建单元格并设置样式,值
     *
     * @param row
     * @param column
     * @param value
     * @param cellStyle
     */
    public static void createCell(Row row, int column, String value, CellStyle cellStyle) {
        if (row != null) {
            Cell cell = row.createCell(column);
            cell.setCellValue(value);
            cell.setCellStyle(cellStyle);
        }
    }

//	public static void createCell1(HSSFWorkbook wb, Row row, int column,
//			String value, Font font1) {
//		Cell cell = row.createCell(column);
//        cell.setCellValue(value);
//		CellStyle cellStyle = wb.createCellStyle();
//        cellStyle.setFont(font1);
//        cell.setCellStyle(cellStyle); //注意这个问题The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
//	}

    /**
     * 设置字体
     *
     * @param wb
     * @return
     */
    public static Font createFonts(org.apache.poi.ss.usermodel.Workbook wb, boolean isBold, String fontName,
                                   boolean isItalic, short hight) {
        Font font = wb.createFont();
        font.setFontName(fontName);
        font.setBold(isBold);
        font.setItalic(isItalic);
        font.setFontHeight(hight);
        return font;
    }

    public static String getCellValue(Row row, int i) {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);

        if (cell != null) {
            String s = cell.toString().trim();
            if ("未知".equals(s) || "".equals(s)) {
                s = null;
            }
            return s;
        } else {
            return null;
        }
    }

    /**
     * excel 数据 类型，整型,字符串 转换
     *
     * @param row
     * @param i
     * @return
     */
    public static String getCellValue2String(Row row, int i) {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);
        if (cell != null) {
            String s = null;

            if (CellType.STRING == cell.getCellType()) {
                s = cell.toString().trim();
            } else if (CellType.NUMERIC == cell.getCellType()) {
                s = String.valueOf(PoiExcelUtil.getCellIntValue(row, i));
            }
            if ("未知".equals(s) || "".equals(s)) {
                s = null;
            }
            return s;
        } else {
            return null;
        }
    }

    public static String getNumericValue2String(Row row, int i) {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);
        if (cell != null) {
            String s = null;
            if (CellType.STRING == cell.getCellType()) {
                s = cell.toString().trim();
            } else if (CellType.NUMERIC == cell.getCellType()) {
                s = NUMBER_FORMAT.format(cell.getNumericCellValue());
                //有可能出现逗号进行分割的情况，例如2,400，需要将逗号删除
                if (s != null) {
                    s = s.replace(",", "");
                }
            }
            if ("未知".equals(s) || "".equals(s)) {
                s = null;
            }
            return s;
        } else {
            return null;
        }
    }

    public static int getCellIntValue(Row row, int i) {
        if (row == null) {
            return 0;
        }
        Cell cell = row.getCell(i);

        if (cell != null) {
            Integer s = null;
            try {
                s = (int) cell.getNumericCellValue();
            } catch (Exception e) {
                String ss = cell.getStringCellValue();
                if (!HlpUtils.isEmpty(ss)) {
                    ss = ss.trim().replace("\n", "").replace("\r", "");
                    s = Integer.parseInt(ss);
                } else {
                    s = 0;
                }
            }
            return s;
        } else {
            return 0;
        }
    }

    public static Date getDateCellValue(Row row, int i) throws Exception {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);

        if (cell != null) {
            Date d = null;
            try {
                d = cell.getDateCellValue();
            } catch (Exception e) {
                String ds = cell.getStringCellValue();
                ds = ds.trim().replace("\n", "").replace("\r", "");
                d = HlpUtils.string2Date(ds, HlpUtils.Format_Yyyy_M_d);
            }
            return d;
        } else {
            return null;
        }
    }

    public static Double getDoubleCellValue(Row row, int i) {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);

        if (cell != null) {
            double d;
            try {
                String value = NUMBER_FORMAT.format(cell.getNumericCellValue());
                //有可能出现逗号进行分割的情况，例如2,400，需要将逗号删除
                if (value != null) {
                    value = value.replace(",", "");
                }
                d = Double.parseDouble(value);
            } catch (Exception e) {
                String ds = cell.getStringCellValue();
                if (!HlpUtils.isEmpty(ds)) {
                    ds = ds.trim().replace("\n", "").replace("\r", "");
                    d = Double.parseDouble(ds);
                } else {
                    d = 0D;
                }
            }
            return d;
        } else {
            return null;
        }
    }
}
